/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.*;


import java.util.Vector;

/**
 *
 * @author Venus
 */
public class DauBep_Model {

    public Vector LayDSMACook(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA in (1, 2, 4) "
                    + " Order by MaCTHDT asc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector CookInteval(String makv, String tenban, String tenma, String tendvt, String soluong, String mattma, String ng, String kv, String ban, String ma, String dvt, String sl, String ud1, String ud2, String ud3, String ud4, String ud5, String ud6,  int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA in (1, 2, 4) ";
            String[] arr = {makv, tenban, tenma, tendvt, soluong, mattma};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makv)) {
                    query = query + " and C.MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenma)) {
                    query = query + " and TenMa like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soluong)) {
                    query = query + " and SoLuong = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(mattma)) {
                    query = query + " and A.MaTTMA = " + arr[i] + " ";
                }
            }
            String y = " Order by "; 
                    y = y + " GioVao "+ud1+" , ";
                    y = y + " TenKV "+ud2+" , ";                    
                    y = y + " TenBan "+ud3+" , ";
                    y = y + " TenMA "+ud4+" , ";
                    y = y + " TenDVT "+ud5+" , ";
                    y = y + " SoLuong "+ud6+" , "; 
                query = query + y.substring(0, y.length() - 2);
            
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayDSMACook3(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA = 3 "
                    + " Order by MaCTHDT asc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayTTMA(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select * from tinhtrangmonan where MaNN = " + langid + " ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TinhTrangMA b = new Beans.TinhTrangMA();
                b.setMaTTMA(rss.getInt(1));
                b.setMaNN(rss.getInt(2));
                b.setTenTTMA(rss.getString(3));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector LayDSHoiThuc(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaCTHDT, TenMA, MaDVT, GhiChu, SoLuong, A.MaMA from chitietpdma A, monan B " +
                           " where A.MaMA = B.MaMA and B.MaNN = "+langid+" and HoiThuc = 1 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                b.setMaCTHDT(rss.getInt(1));
                b.setTenMA(rss.getString(2));
                b.setMaDVT(rss.getInt(3));
                b.setGhiChu(rss.getString(4));
                b.setSoLuong(rss.getInt(5));
                b.setMaMA(rss.getInt(6));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean UpdateTTMACook(int MaCTHDT, int MaTTMA) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "";
            if(MaTTMA == 1){
                query = "Update chitietpdma set MaTTMA = " + MaTTMA + " where MaCTHDT = " + MaCTHDT;
            }
            else{
                query = "Update chitietpdma set MaTTMA = " + MaTTMA + ", HoiThuc = 0 where MaCTHDT = " + MaCTHDT;
            }
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean BoQuaHoiThuc(int MaCTHDT) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "";
            query = "Update chitietpdma set HoiThuc = 0 where MaCTHDT = " + MaCTHDT;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean UpdateTTCook(int TT, int MaMA, int MaCTHDT) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query1 = "";
            if(TT == 1){
                query1 = " Update chitietpdma set MaTTMA = 1 where MaCTHDT = "+MaCTHDT+" ";
            }
            else{
                query1 = " Update chitietpdma set MaTTMA = 4 where MaCTHDT = "+MaCTHDT+" ";
            }
            String query = "Update monan set CoPhucVu = " + TT + " where MaMA = " + MaMA;
            stmt.executeUpdate(query1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector SortNgayGioCook(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA in (1, 2, 4) "
                    + " Order by GioVao "+order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortNgayGioCook3(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA = 3 "
                    + " Order by GioVao " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortKVCook(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA in (1, 2, 4) "
                    + " Order by TenKV " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortKVCook3(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA = 3 "
                    + " Order by TenKV " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortBanCook(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA in (1, 2, 4) "
                    + " Order by TenBan " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortBanCook3(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA = 3 "
                    + " Order by TenBan " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortMACook(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA in (1, 2, 4) "
                    + " Order by TenMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortMACook3(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA = 3 "
                    + " Order by TenMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortDVTCook(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA in (1, 2, 4) "
                    + " Order by TenDVT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortDVTCook3(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA = 3 "
                    + " Order by TenDVT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSLCook(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA in (1, 2, 4) "
                    + " Order by SoLuong " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSLCook3(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA = 3 "
                    + " Order by SoLuong " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTTCook(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA in (1, 2, 4) "
                    + " Order by A.MaTTMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTTCook3(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" and  A.MaTTMA = 3 "
                    + " Order by A.MaTTMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SearchCook(String makv, String tenban, String tenma, String tendvt, String soluong, String mattma, int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" ";
            String[] arr = {makv, tenban, tenma, tendvt, soluong, mattma};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makv)) {
                    query = query + " and C.MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenma)) {
                    query = query + " and TenMa like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soluong)) {
                    query = query + " and SoLuong = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(mattma)) {
                    query = query + " and A.MaTTMA = " + arr[i] + " ";
                }
            }
            query = query + " Order by MaCTHDT asc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchNgayGioCook(String makv, String tenban, String tenma, String tendvt, String soluong, String mattma, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" ";
            String[] arr = {makv, tenban, tenma, tendvt, soluong, mattma};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makv)) {
                    query = query + " and C.MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenma)) {
                    query = query + " and TenMa like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soluong)) {
                    query = query + " and SoLuong = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(mattma)) {
                    query = query + " and A.MaTTMA = " + arr[i] + " ";
                }
            }
            query = query + " Order by GioVao " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchKVCook(String makv, String tenban, String tenma, String tendvt, String soluong, String mattma, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+"  ";
            String[] arr = {makv, tenban, tenma, tendvt, soluong, mattma};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makv)) {
                    query = query + " and C.MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenma)) {
                    query = query + " and TenMa like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soluong)) {
                    query = query + " and SoLuong = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(mattma)) {
                    query = query + " and A.MaTTMA = " + arr[i] + " ";
                }
            }
            query = query + " Order by TenKV " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchBanCook(String makv, String tenban, String tenma, String tendvt, String soluong, String mattma, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" ";
            String[] arr = {makv, tenban, tenma, tendvt, soluong, mattma};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makv)) {
                    query = query + " and C.MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenma)) {
                    query = query + " and TenMa like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soluong)) {
                    query = query + " and SoLuong = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(mattma)) {
                    query = query + " and A.MaTTMA = " + arr[i] + " ";
                }
            }
            query = query + " Order by TenBan " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchMACook(String makv, String tenban, String tenma, String tendvt, String soluong, String mattma, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" ";
            String[] arr = {makv, tenban, tenma, tendvt, soluong, mattma};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makv)) {
                    query = query + " and C.MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenma)) {
                    query = query + " and TenMa like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soluong)) {
                    query = query + " and SoLuong = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(mattma)) {
                    query = query + " and A.MaTTMA = " + arr[i] + " ";
                }
            }
            query = query + " Order by TenMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchDVTCook(String makv, String tenban, String tenma, String tendvt, String soluong, String mattma, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" ";
            String[] arr = {makv, tenban, tenma, tendvt, soluong, mattma};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makv)) {
                    query = query + " and C.MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenma)) {
                    query = query + " and TenMa like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soluong)) {
                    query = query + " and SoLuong = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(mattma)) {
                    query = query + " and A.MaTTMA = " + arr[i] + " ";
                }
            }
            query = query + " Order by TenDVT " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchSLCook(String makv, String tenban, String tenma, String tendvt, String soluong, String mattma, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+" ";
            String[] arr = {makv, tenban, tenma, tendvt, soluong, mattma};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makv)) {
                    query = query + " and C.MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenma)) {
                    query = query + " and TenMa like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soluong)) {
                    query = query + " and SoLuong = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(mattma)) {
                    query = query + " and A.MaTTMA = " + arr[i] + " ";
                }
            }
            query = query + " Order by SoLuong " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTTCook(String makv, String tenban, String tenma, String tendvt, String soluong, String mattma, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select GioVao, TenKV, TenBan, TenMA, TenDVT, SoLuong, GhiChu, A.MaTTMA, CoPhucVu, MaCTHDT, A.MaMA, A.MaDVT, TenTTMA "
                    + " from chitietpdma A, phieudatmonan B, KhuVuc C, Ban D, DonViTinh E, MonAn F, tinhtrangmonan G  "
                    + " where A.SoHDT = B.SoHDT and C.MaKV = D.MaKV and B.MaBan = D.MaBan and A.MaDVT = E.MaDVT and A.MaMA = F.MaMA and A.MaTTMA = G.MaTTMA "
                    + "   and F.MaNN = " + langid + " and E.MaNN = " + langid + " and C.MaNN = " + langid + " and G.MaNN = "+langid+"  ";
            String[] arr = {makv, tenban, tenma, tendvt, soluong, mattma};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(makv)) {
                    query = query + " and C.MaKV = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenban)) {
                    query = query + " and TenBan = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tenma)) {
                    query = query + " and TenMa like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tendvt)) {
                    query = query + " and TenDVT like '%" + arr[i] + "%' ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(soluong)) {
                    query = query + " and SoLuong = " + arr[i] + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(mattma)) {
                    query = query + " and A.MaTTMA = " + arr[i] + " ";
                }
            }
            query = query + " Order by A.MaTTMA " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.DauBep b = new Beans.DauBep();
                String gc = rss.getString(7);
                String note = "";
                String ghichu[] = gc.split("&&");
                if (!ghichu[0].equals("")) {
                    String x = TuyChon_Model.LayTenTCPDMT(ghichu[0], langid);
                    if(ghichu[1].trim().equals("")){
                        note = note + x.substring(0, x.length() - 2);
                    }
                    else{
                        note = note + x + ghichu[1];
                    }
                } else {
                    note = ghichu[1];
                }
                b.setNgayGio(rss.getString(1));
                b.setTenKV(rss.getString(2));
                b.setTenBan(rss.getString(3));
                b.setTenMA(rss.getString(4));
                b.setTenDVT(rss.getString(5));
                b.setSoLuong(rss.getInt(6));
                b.setGhiChu(note);
                b.setMaTTMA(rss.getInt(8));
                b.setCoPhucVu(rss.getByte(9));
                b.setMaCTHDT(rss.getInt(10));
                b.setMaMA(rss.getInt(11));
                b.setMaDVT(rss.getInt(12));
                b.setTenTTMA(rss.getString(13));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static int Count() {
        int n = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select count(*) from phieudatmonan where MaTTMA in (1, 2) ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return n;
    }
    public static String LayTenTTMA(String mattma, int langid) {
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            Connection conn = pool.getConnection();
            ResultSet rss = null;
            Statement stmt = conn.createStatement();
            String query = "select TenTTMA from tinhtrangmonan where  MaTTMA = " + mattma+ " and MaNN = "+langid+" ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return maht;
    }
    public static int HoiThuc() {
        int maht = 0;
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select count(*) from chitietpdma where HoiThuc = 1 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }
    public static Vector DSDauBep() {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select MaNV, HoTen from nhanvien where MaNNV = 4 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.NhanVien b = new Beans.NhanVien();
                b.setMaNV(rss.getInt(1));
                b.setHoTen(rss.getString(2));
                v.add(b);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }
    public static boolean UpdateCTPDMA(int MaNVDB, int MaCTHDT) {
        Connection conn = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update chitietpdma set MaNVDauBep = " + MaNVDB + " where MaCTHDT = " + MaCTHDT;
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

}
